package com.groot.medical.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.groot.common.util.MedicalDBUtil;
import com.groot.common.util.SimpleDateUtil;
import com.groot.medical.entity.ChronicExpense;
import com.groot.medical.entity.PersonalFiles;

public class ChronicExpenseDAOImpl implements ChronicExpenseDAO {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	@Override
	public double findAlreadyExpenseMoeny(String idcardNumber, String nowTime) {
		double count = 0;
        //sql拼接
		StringBuffer sql = new StringBuffer("SELECT SUM(expense_money) id FROM t_chronic_expense WHERE idcard_numeber=? AND current_year=? AND is_pass = 1 AND is_remittance = 1");
        //查询结果
        try {
        	 conn = MedicalDBUtil.getConnection();
             ps = conn.prepareStatement(sql.toString());
             ps.setString(1, idcardNumber);
             ps.setString(2, nowTime);
             rs = ps.executeQuery();
            if(rs.next()) {
                count = rs.getInt("id");
            }
        } catch (SQLException e) {

    		e.printStackTrace();
    		}
    		finally{
    			MedicalDBUtil.closeAll(conn, ps, rs);
    		}
        return count;
	}
	@Override
	public void defineExpenseMoney(ChronicExpense expense) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="insert into t_chronic_expense(idcard_numeber,farmingcard_code,expense_money,expense_time,current_year,hospital_ticket,tiket_code,ill_code,is_pass,is_remittance)values(?,?,?,?,?,?,?,?,?,?)";
			ps=conn.prepareStatement(sql);
			int start = 1;
			ps.setString(start++,expense.getIdCardNumber());
			ps.setString(start++,expense.getFarmingCardCode());
			ps.setDouble(start++,expense.getExpenseMoney());
			ps.setTimestamp(start++,expense.getExpenseTime());
			ps.setString(start++,expense.getCurrentYear());
			ps.setString(start++,expense.getHospitalTicket());
			ps.setString(start++,expense.getTicketCode());
			ps.setString(start++,expense.getIllCode());
			ps.setInt(start++,expense.getIsPass());
			ps.setInt(start++,expense.getIsRemittance());
		
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}
	@Override
	public int passExpense(String id) {
		int i =0;
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			//UPDATE student SET name='"+name+"', age=" + age+" where name='大红'";
			String sql="update t_chronic_expense set is_pass=1 where id=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, id);
		
			i= ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
			return i;
	}
	@Override
	public int RemittanceExpense(String id) {
		int i =0;
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			//UPDATE student SET name='"+name+"', age=" + age+" where name='大红'";
			String sql="update t_chronic_expense set is_remittance=1 where id=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, id);
		
			i= ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
			return i;
	}
	@Override
	public List<PersonalFiles> findPersonByIdCardNum(String idCardNum) {
		StringBuffer sql = new StringBuffer("SELECT p.`name`,f.family_address,p.`farmingcard_code`,p.idcard_number FROM t_personnel_file p,t_family_rchives f WHERE p.family_code=f.family_code  AND p.idcard_number LIKE '%"+idCardNum+"%'");
		
		List<PersonalFiles> persons = new ArrayList<>();
		 try {
				conn = MedicalDBUtil.getConnection();
				ps = conn.prepareStatement(sql.toString());
				
				rs = ps.executeQuery();
				while(rs.next()){
					PersonalFiles person = new PersonalFiles();
					person.setName(rs.getString(1));
					person.setOftenAddress(rs.getString(2));
					person.setFarmingcardCode(rs.getString(3));
					person.setIdcardNumber(rs.getString(4));
					persons.add(person);
					}
				return persons;
		 }catch (SQLException e) {
					e.printStackTrace();
					}
		 			finally{
					MedicalDBUtil.closeAll(conn, ps, rs);
				}
		 return null;
	}

}
